{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>部门</th>\n",
       "      <th>姓名</th>\n",
       "      <th>工资</th>\n",
       "      <th>部门2</th>\n",
       "      <th>姓名3</th>\n",
       "      <th>工资4</th>\n",
       "      <th>部门5</th>\n",
       "      <th>姓名6</th>\n",
       "      <th>工资7</th>\n",
       "      <th>部门8</th>\n",
       "      <th>姓名9</th>\n",
       "      <th>工资10</th>\n",
       "      <th>部门11</th>\n",
       "      <th>姓名12</th>\n",
       "      <th>工资13</th>\n",
       "      <th>部门14</th>\n",
       "      <th>姓名15</th>\n",
       "      <th>工资16</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>财务部</td>\n",
       "      <td>严颖</td>\n",
       "      <td>6749</td>\n",
       "      <td>企管部</td>\n",
       "      <td>王浩然</td>\n",
       "      <td>6890</td>\n",
       "      <td>科技部</td>\n",
       "      <td>韩丽</td>\n",
       "      <td>5604.0</td>\n",
       "      <td>生产部</td>\n",
       "      <td>孙英</td>\n",
       "      <td>7574.0</td>\n",
       "      <td>科技部</td>\n",
       "      <td>陶盛林</td>\n",
       "      <td>7971.0</td>\n",
       "      <td>财务部</td>\n",
       "      <td>褚亨</td>\n",
       "      <td>5155.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>采购部</td>\n",
       "      <td>姜程燕</td>\n",
       "      <td>4633</td>\n",
       "      <td>生产部</td>\n",
       "      <td>李伟</td>\n",
       "      <td>7921</td>\n",
       "      <td>生产部</td>\n",
       "      <td>华文静</td>\n",
       "      <td>5195.0</td>\n",
       "      <td>_采购部</td>\n",
       "      <td>吕进</td>\n",
       "      <td>7655.0</td>\n",
       "      <td>科技部</td>\n",
       "      <td>安浪</td>\n",
       "      <td>4046.0</td>\n",
       "      <td>市场部</td>\n",
       "      <td>王胜民</td>\n",
       "      <td>7124.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>采购部</td>\n",
       "      <td>秦凡</td>\n",
       "      <td>7918</td>\n",
       "      <td>品牌部</td>\n",
       "      <td>张俊龙</td>\n",
       "      <td>5077</td>\n",
       "      <td>_企管部</td>\n",
       "      <td>朱夜白</td>\n",
       "      <td>6943.0</td>\n",
       "      <td>企管部</td>\n",
       "      <td>许睿敏</td>\n",
       "      <td>7329.0</td>\n",
       "      <td>品牌部</td>\n",
       "      <td>魏启锋_</td>\n",
       "      <td>6571.0</td>\n",
       "      <td>企管部</td>\n",
       "      <td>郑欢</td>\n",
       "      <td>4908.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>品牌部</td>\n",
       "      <td>华凡</td>\n",
       "      <td>7111</td>\n",
       "      <td>市场部</td>\n",
       "      <td>姜向阳</td>\n",
       "      <td>7705</td>\n",
       "      <td>市场部</td>\n",
       "      <td>严裕</td>\n",
       "      <td>4156.0</td>\n",
       "      <td>宣传部</td>\n",
       "      <td>卫永珍</td>\n",
       "      <td>7718.0</td>\n",
       "      <td>市场部</td>\n",
       "      <td>吴骅</td>\n",
       "      <td>4572.0</td>\n",
       "      <td>市场部</td>\n",
       "      <td>王蓓_</td>\n",
       "      <td>6020.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>财务部</td>\n",
       "      <td>卫浩镜</td>\n",
       "      <td>6447</td>\n",
       "      <td>科技部</td>\n",
       "      <td>乐宪杰</td>\n",
       "      <td>5876</td>\n",
       "      <td>生产部</td>\n",
       "      <td>魏朋</td>\n",
       "      <td>6967.0</td>\n",
       "      <td>市场部</td>\n",
       "      <td>秦胡强</td>\n",
       "      <td>6445.0</td>\n",
       "      <td>采购部</td>\n",
       "      <td>孙建华</td>\n",
       "      <td>7863.0</td>\n",
       "      <td>党政部</td>\n",
       "      <td>乌礼杰</td>\n",
       "      <td>7531.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    部门   姓名    工资  部门2  姓名3   工资4   部门5  姓名6     工资7   部门8  姓名9    工资10 部门11  \\\n",
       "0  财务部   严颖  6749  企管部  王浩然  6890   科技部   韩丽  5604.0   生产部   孙英  7574.0  科技部   \n",
       "1  采购部  姜程燕  4633  生产部   李伟  7921   生产部  华文静  5195.0  _采购部   吕进  7655.0  科技部   \n",
       "2  采购部   秦凡  7918  品牌部  张俊龙  5077  _企管部  朱夜白  6943.0   企管部  许睿敏  7329.0  品牌部   \n",
       "3  品牌部   华凡  7111  市场部  姜向阳  7705   市场部   严裕  4156.0   宣传部  卫永珍  7718.0  市场部   \n",
       "4  财务部  卫浩镜  6447  科技部  乐宪杰  5876   生产部   魏朋  6967.0   市场部  秦胡强  6445.0  采购部   \n",
       "\n",
       "   姓名12    工资13 部门14 姓名15    工资16  \n",
       "0   陶盛林  7971.0  财务部   褚亨  5155.0  \n",
       "1    安浪  4046.0  市场部  王胜民  7124.0  \n",
       "2  魏启锋_  6571.0  企管部   郑欢  4908.0  \n",
       "3    吴骅  4572.0  市场部  王蓓_  6020.0  \n",
       "4   孙建华  7863.0  党政部  乌礼杰  7531.0  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 读取数据集\n",
    "df = pd.read_excel('./案例.xlsx',sheet_name='Sheet1')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['部门',\n",
       " '姓名',\n",
       " '工资',\n",
       " '部门2',\n",
       " '姓名3',\n",
       " '工资4',\n",
       " '部门5',\n",
       " '姓名6',\n",
       " '工资7',\n",
       " '部门8',\n",
       " '姓名9',\n",
       " '工资10',\n",
       " '部门11',\n",
       " '姓名12',\n",
       " '工资13',\n",
       " '部门14',\n",
       " '姓名15',\n",
       " '工资16']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 提取待合并的所有列名\n",
    "merge_names = list(df.columns.values)\n",
    "merge_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>部门</th>\n",
       "      <th>姓名</th>\n",
       "      <th>工资</th>\n",
       "      <th>部门2</th>\n",
       "      <th>姓名3</th>\n",
       "      <th>工资4</th>\n",
       "      <th>部门5</th>\n",
       "      <th>姓名6</th>\n",
       "      <th>工资7</th>\n",
       "      <th>部门8</th>\n",
       "      <th>姓名9</th>\n",
       "      <th>工资10</th>\n",
       "      <th>部门11</th>\n",
       "      <th>姓名12</th>\n",
       "      <th>工资13</th>\n",
       "      <th>部门14</th>\n",
       "      <th>姓名15</th>\n",
       "      <th>工资16</th>\n",
       "      <th>merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>财务部</td>\n",
       "      <td>严颖</td>\n",
       "      <td>6749</td>\n",
       "      <td>企管部</td>\n",
       "      <td>王浩然</td>\n",
       "      <td>6890</td>\n",
       "      <td>科技部</td>\n",
       "      <td>韩丽</td>\n",
       "      <td>5604.0</td>\n",
       "      <td>生产部</td>\n",
       "      <td>孙英</td>\n",
       "      <td>7574.0</td>\n",
       "      <td>科技部</td>\n",
       "      <td>陶盛林</td>\n",
       "      <td>7971.0</td>\n",
       "      <td>财务部</td>\n",
       "      <td>褚亨</td>\n",
       "      <td>5155.0</td>\n",
       "      <td>财务部|严颖|6749#企管部|王浩然|6890#科技部|韩丽|5604.0#生产部|孙英|...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>采购部</td>\n",
       "      <td>姜程燕</td>\n",
       "      <td>4633</td>\n",
       "      <td>生产部</td>\n",
       "      <td>李伟</td>\n",
       "      <td>7921</td>\n",
       "      <td>生产部</td>\n",
       "      <td>华文静</td>\n",
       "      <td>5195.0</td>\n",
       "      <td>_采购部</td>\n",
       "      <td>吕进</td>\n",
       "      <td>7655.0</td>\n",
       "      <td>科技部</td>\n",
       "      <td>安浪</td>\n",
       "      <td>4046.0</td>\n",
       "      <td>市场部</td>\n",
       "      <td>王胜民</td>\n",
       "      <td>7124.0</td>\n",
       "      <td>采购部|姜程燕|4633#生产部|李伟|7921#生产部|华文静|5195.0#_采购部|吕...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>采购部</td>\n",
       "      <td>秦凡</td>\n",
       "      <td>7918</td>\n",
       "      <td>品牌部</td>\n",
       "      <td>张俊龙</td>\n",
       "      <td>5077</td>\n",
       "      <td>_企管部</td>\n",
       "      <td>朱夜白</td>\n",
       "      <td>6943.0</td>\n",
       "      <td>企管部</td>\n",
       "      <td>许睿敏</td>\n",
       "      <td>7329.0</td>\n",
       "      <td>品牌部</td>\n",
       "      <td>魏启锋_</td>\n",
       "      <td>6571.0</td>\n",
       "      <td>企管部</td>\n",
       "      <td>郑欢</td>\n",
       "      <td>4908.0</td>\n",
       "      <td>采购部|秦凡|7918#品牌部|张俊龙|5077#_企管部|朱夜白|6943.0#企管部|许...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>品牌部</td>\n",
       "      <td>华凡</td>\n",
       "      <td>7111</td>\n",
       "      <td>市场部</td>\n",
       "      <td>姜向阳</td>\n",
       "      <td>7705</td>\n",
       "      <td>市场部</td>\n",
       "      <td>严裕</td>\n",
       "      <td>4156.0</td>\n",
       "      <td>宣传部</td>\n",
       "      <td>卫永珍</td>\n",
       "      <td>7718.0</td>\n",
       "      <td>市场部</td>\n",
       "      <td>吴骅</td>\n",
       "      <td>4572.0</td>\n",
       "      <td>市场部</td>\n",
       "      <td>王蓓_</td>\n",
       "      <td>6020.0</td>\n",
       "      <td>品牌部|华凡|7111#市场部|姜向阳|7705#市场部|严裕|4156.0#宣传部|卫永珍...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>财务部</td>\n",
       "      <td>卫浩镜</td>\n",
       "      <td>6447</td>\n",
       "      <td>科技部</td>\n",
       "      <td>乐宪杰</td>\n",
       "      <td>5876</td>\n",
       "      <td>生产部</td>\n",
       "      <td>魏朋</td>\n",
       "      <td>6967.0</td>\n",
       "      <td>市场部</td>\n",
       "      <td>秦胡强</td>\n",
       "      <td>6445.0</td>\n",
       "      <td>采购部</td>\n",
       "      <td>孙建华</td>\n",
       "      <td>7863.0</td>\n",
       "      <td>党政部</td>\n",
       "      <td>乌礼杰</td>\n",
       "      <td>7531.0</td>\n",
       "      <td>财务部|卫浩镜|6447#科技部|乐宪杰|5876#生产部|魏朋|6967.0#市场部|秦胡...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    部门   姓名    工资  部门2  姓名3   工资4   部门5  姓名6     工资7   部门8  姓名9    工资10 部门11  \\\n",
       "0  财务部   严颖  6749  企管部  王浩然  6890   科技部   韩丽  5604.0   生产部   孙英  7574.0  科技部   \n",
       "1  采购部  姜程燕  4633  生产部   李伟  7921   生产部  华文静  5195.0  _采购部   吕进  7655.0  科技部   \n",
       "2  采购部   秦凡  7918  品牌部  张俊龙  5077  _企管部  朱夜白  6943.0   企管部  许睿敏  7329.0  品牌部   \n",
       "3  品牌部   华凡  7111  市场部  姜向阳  7705   市场部   严裕  4156.0   宣传部  卫永珍  7718.0  市场部   \n",
       "4  财务部  卫浩镜  6447  科技部  乐宪杰  5876   生产部   魏朋  6967.0   市场部  秦胡强  6445.0  采购部   \n",
       "\n",
       "   姓名12    工资13 部门14 姓名15    工资16  \\\n",
       "0   陶盛林  7971.0  财务部   褚亨  5155.0   \n",
       "1    安浪  4046.0  市场部  王胜民  7124.0   \n",
       "2  魏启锋_  6571.0  企管部   郑欢  4908.0   \n",
       "3    吴骅  4572.0  市场部  王蓓_  6020.0   \n",
       "4   孙建华  7863.0  党政部  乌礼杰  7531.0   \n",
       "\n",
       "                                               merge  \n",
       "0  财务部|严颖|6749#企管部|王浩然|6890#科技部|韩丽|5604.0#生产部|孙英|...  \n",
       "1  采购部|姜程燕|4633#生产部|李伟|7921#生产部|华文静|5195.0#_采购部|吕...  \n",
       "2  采购部|秦凡|7918#品牌部|张俊龙|5077#_企管部|朱夜白|6943.0#企管部|许...  \n",
       "3  品牌部|华凡|7111#市场部|姜向阳|7705#市场部|严裕|4156.0#宣传部|卫永珍...  \n",
       "4  财务部|卫浩镜|6447#科技部|乐宪杰|5876#生产部|魏朋|6967.0#市场部|秦胡...  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "def merge_cols(x):\n",
    "    \"\"\"\n",
    "    x是一个行Series，把它们按分隔符合并\n",
    "    \"\"\"\n",
    "    # 删除为空的列\n",
    "    x = x[x.notna()]\n",
    "    # 使用x.values用于合并\n",
    "    y = x.values\n",
    "    # 合并后的列表，每个元素是\"Supplier\" + \"Supplier PN\"对\n",
    "    result = []\n",
    "    # range的步长为 3，目的是每 3 列做合并\n",
    "    for idx in range(0, len(y), 3):\n",
    "        # 使用竖线作为\"部门\" + \"项目\" + \"工资\"之间的分隔符\n",
    "        result.append(f\"{y[idx]}|{y[idx+1]}|{y[idx+2]}\")\n",
    "    # 将所有两两对，用#分割，返回一个大字符串\n",
    "    return \"#\".join(result)\n",
    "\n",
    "# 添加新列，把待合并的所有列变成一个大字符串\n",
    "df[\"merge\"] = df.apply(merge_cols, axis=1)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>财务部|严颖|6749#企管部|王浩然|6890#科技部|韩丽|5604.0#生产部|孙英|...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>采购部|姜程燕|4633#生产部|李伟|7921#生产部|华文静|5195.0#_采购部|吕...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>采购部|秦凡|7918#品牌部|张俊龙|5077#_企管部|朱夜白|6943.0#企管部|许...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>品牌部|华凡|7111#市场部|姜向阳|7705#市场部|严裕|4156.0#宣传部|卫永珍...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>财务部|卫浩镜|6447#科技部|乐宪杰|5876#生产部|魏朋|6967.0#市场部|秦胡...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               merge\n",
       "0  财务部|严颖|6749#企管部|王浩然|6890#科技部|韩丽|5604.0#生产部|孙英|...\n",
       "1  采购部|姜程燕|4633#生产部|李伟|7921#生产部|华文静|5195.0#_采购部|吕...\n",
       "2  采购部|秦凡|7918#品牌部|张俊龙|5077#_企管部|朱夜白|6943.0#企管部|许...\n",
       "3  品牌部|华凡|7111#市场部|姜向阳|7705#市场部|严裕|4156.0#宣传部|卫永珍...\n",
       "4  财务部|卫浩镜|6447#科技部|乐宪杰|5876#生产部|魏朋|6967.0#市场部|秦胡..."
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 把不用的列删除掉\n",
    "df.drop(merge_names, axis=1, inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[财务部|严颖|6749, 企管部|王浩然|6890, 科技部|韩丽|5604.0, 生产部...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[采购部|姜程燕|4633, 生产部|李伟|7921, 生产部|华文静|5195.0, _采...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[采购部|秦凡|7918, 品牌部|张俊龙|5077, _企管部|朱夜白|6943.0, 企...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[品牌部|华凡|7111, 市场部|姜向阳|7705, 市场部|严裕|4156.0, 宣传部...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>[财务部|卫浩镜|6447, 科技部|乐宪杰|5876, 生产部|魏朋|6967.0, 市场...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               merge\n",
       "0  [财务部|严颖|6749, 企管部|王浩然|6890, 科技部|韩丽|5604.0, 生产部...\n",
       "1  [采购部|姜程燕|4633, 生产部|李伟|7921, 生产部|华文静|5195.0, _采...\n",
       "2  [采购部|秦凡|7918, 品牌部|张俊龙|5077, _企管部|朱夜白|6943.0, 企...\n",
       "3  [品牌部|华凡|7111, 市场部|姜向阳|7705, 市场部|严裕|4156.0, 宣传部...\n",
       "4  [财务部|卫浩镜|6447, 科技部|乐宪杰|5876, 生产部|魏朋|6967.0, 市场..."
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 先将merge列变成list的形式\n",
    "df[\"merge\"] = df[\"merge\"].str.split(\"#\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>财务部|严颖|6749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>企管部|王浩然|6890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>科技部|韩丽|5604.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>生产部|孙英|7574.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>科技部|陶盛林|7971.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            merge\n",
       "0     财务部|严颖|6749\n",
       "0    企管部|王浩然|6890\n",
       "0   科技部|韩丽|5604.0\n",
       "0   生产部|孙英|7574.0\n",
       "0  科技部|陶盛林|7971.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 执行explode变成多行\n",
    "df_explode = df.explode(\"merge\")\n",
    "df_explode.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>merge</th>\n",
       "      <th>部门</th>\n",
       "      <th>姓名</th>\n",
       "      <th>工资</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>财务部|严颖|6749</td>\n",
       "      <td>财务部</td>\n",
       "      <td>严颖</td>\n",
       "      <td>6749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>企管部|王浩然|6890</td>\n",
       "      <td>企管部</td>\n",
       "      <td>王浩然</td>\n",
       "      <td>6890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>科技部|韩丽|5604.0</td>\n",
       "      <td>科技部</td>\n",
       "      <td>韩丽</td>\n",
       "      <td>5604.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>生产部|孙英|7574.0</td>\n",
       "      <td>生产部</td>\n",
       "      <td>孙英</td>\n",
       "      <td>7574.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>科技部|陶盛林|7971.0</td>\n",
       "      <td>科技部</td>\n",
       "      <td>陶盛林</td>\n",
       "      <td>7971.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            merge   部门   姓名      工资\n",
       "0     财务部|严颖|6749  财务部   严颖    6749\n",
       "0    企管部|王浩然|6890  企管部  王浩然    6890\n",
       "0   科技部|韩丽|5604.0  科技部   韩丽  5604.0\n",
       "0   生产部|孙英|7574.0  生产部   孙英  7574.0\n",
       "0  科技部|陶盛林|7971.0  科技部  陶盛林  7971.0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 分别从merge中提取 3 列\n",
    "df_explode[\"部门\"]=df_explode[\"merge\"].str.split(\"|\").str[0]\n",
    "df_explode[\"姓名\"]=df_explode[\"merge\"].str.split(\"|\").str[1]\n",
    "df_explode[\"工资\"]=df_explode[\"merge\"].str.split(\"|\").str[2]\n",
    "df_explode.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>部门</th>\n",
       "      <th>姓名</th>\n",
       "      <th>工资</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>财务部</td>\n",
       "      <td>严颖</td>\n",
       "      <td>6749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>企管部</td>\n",
       "      <td>王浩然</td>\n",
       "      <td>6890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>科技部</td>\n",
       "      <td>韩丽</td>\n",
       "      <td>5604.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>生产部</td>\n",
       "      <td>孙英</td>\n",
       "      <td>7574.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>科技部</td>\n",
       "      <td>陶盛林</td>\n",
       "      <td>7971.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>采购部</td>\n",
       "      <td>陈朝海</td>\n",
       "      <td>4255.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>品牌部</td>\n",
       "      <td>冯来福</td>\n",
       "      <td>4556.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>市场部</td>\n",
       "      <td>卫秀娟</td>\n",
       "      <td>4627.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>党政部</td>\n",
       "      <td>何鹏程</td>\n",
       "      <td>6748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>生产部</td>\n",
       "      <td>赵黑豆</td>\n",
       "      <td>4319</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>170 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     部门   姓名      工资\n",
       "0   财务部   严颖    6749\n",
       "0   企管部  王浩然    6890\n",
       "0   科技部   韩丽  5604.0\n",
       "0   生产部   孙英  7574.0\n",
       "0   科技部  陶盛林  7971.0\n",
       "..  ...  ...     ...\n",
       "27  采购部  陈朝海  4255.0\n",
       "27  品牌部  冯来福  4556.0\n",
       "27  市场部  卫秀娟  4627.0\n",
       "28  党政部  何鹏程    6748\n",
       "28  生产部  赵黑豆    4319\n",
       "\n",
       "[170 rows x 3 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 把merge列删除掉，得到最终数据\n",
    "df_explode.drop(\"merge\", axis=1, inplace=True)\n",
    "df_explode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "保存结果文件 成功！！\n"
     ]
    }
   ],
   "source": [
    "# 持久化\n",
    "df_explode.to_excel('./result.xlsx',sheet_name='result',index=False)\n",
    "print(f'保存结果文件 成功！！')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
